import openpyxl
import xlrd
import json
from xlutils.copy import copy
def get_excel_data(sheetname,casename):
    reslist=[]
    path= "../data/外卖接口用例.xls"
    workbook=xlrd.open_workbook(path,formatting_info=True)   #打开表，保持样式
    # worksheetnames=workbook.sheet_names()  #获取所有的表名
    # print(worksheetnames)
    worksheet=workbook.sheet_by_name(sheetname)    #以名打开指定的某个sheet
    list=worksheet.col_values(0)     #获取第一列所有的值，存列表中
    count=0                          #统计每行的值
    for one in list:
        if(casename in one):
            reqcelldata=worksheet.cell(count,9).value  #请求体
            respdata=worksheet.cell(count,11).value    #返回体
            reslist.append((json.loads(reqcelldata),json.loads(respdata)))   #存进去的时候转换为字典格式存进去
            #reslist.append((reqcelldata, respdata))
        count+=1
    return reslist

def write_excel_data():
    reslist=[]
    path= "../data/外卖接口用例.xls"
    workbook=xlrd.open_workbook(path,formatting_info=True)   #打开表，保持样式
    new_workbook=copy(workbook)
    new_worksheet=new_workbook.get_sheet(0)
    return new_workbook,new_worksheet

if __name__ == '__main__':
    pass
#for one in get_excel_data("登录模块","Login"):
#    print(one)
    print(get_excel_data('我的商铺','listshopping'))